﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.Data.OracleClient;
using ReflectionStudio.Core.Events;
namespace WPFExcelReport
{

    public class WarpMasterData : BaseFunction
    {


        public WarpMasterData()
            : base()
        {
       
        }
        public WarpMasterData(OracleConnection _myConnection, IntexReptServBLClient _proxy)
            : base(_myConnection, _proxy)
        {

        }
        public DataSet LoadTableStructure(DataSet dsTarget)
        {
            try
            {
                while (TableQueue.Count > 0)
                {
                    string TableName = TableQueue.Dequeue() as string;
                    orlReadCommand = OracleDB.CreateCommand();
                    orlReadCommand.CommandText = "SELECT * FROM INTEX." + TableName + " WHERE Rownum<1";
                    dsResult = Connect2Proxy(orlReadCommand);
                    if (dsResult.Tables.Count > 0)
                    {
                        dsResult.Tables[0].TableName = TableName;
                    }
                    foreach (DataTable dtTemp in dsResult.Tables)
                    {
                        dsTarget = ImportDataTable(dsTarget, dtTemp);
                    }
                }
            }
            catch (Exception ex)
            {
                 Tracer.Error(ex.StackTrace, ex); 
            }
            return dsTarget;
        }
        public WarpMasterData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }
        public DataSet GetWarpMaster(DataSet dsTarget, DataRow dwBSFARB)
        {
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT  "
                    + "BSKART"
                    + ",BSFDZ"
                    + ",BSSRFNR"
                    + ",BSFAKTOR"
                   +" FROM INTEX.BSSATZ WHERE BSKART = :ArticleNum";
                orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "BSSATZ";
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }

            }
            catch (Exception ex)
            {
                 Tracer.Error(ex.StackTrace, ex); 
            }
            return dsTarget;
        }
        public DataSet GetWarpColorWay(DataSet dsTarget, DataRow dwBSFARB)
        {
            try
            {
                #region Color
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT  "
                    + "BSFKART"
                    + ",BSFKVAR"
                    + ",BSFKFARBE "
                    + ",BSFBEZ"
                    + ",BSFBEZ as COMMENTS"
                + " FROM INTEX.BSFARB WHERE BSFKART = :ArticleNum AND BSFKVAR=:ArticleVar";
                //AND BSFKFARBE=:ArticleColor";
                orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                orlReadCommand.Parameters.Add("ArticleVar", OracleType.NVarChar).Value = dwBSFARB["BSFKVAR"].ToString();
                //orlReadCommand.Parameters.Add("ArticleColor", OracleType.NVarChar).Value = dwBSFARB["BSFKFARBE"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "BSFARB";
                    dsTarget = ImportDataTable(dsTarget
                      , FilterByColor(
                      dsResult.Tables["BSFARB"],
                      "BSFKFARBE",
                      dwBSFARB["BSFKFARBE"].ToString())
                       );
                }
                #endregion
                #region Comments
                if(dsTarget.Tables["BSFARB"].Rows.Count>0)
                {
                    foreach(DataRow dwTmp in dsTarget.Tables["BSFARB"].Rows)
                    {
                        string WarpArticle=dwTmp["BSFKART"].ToString()+dwTmp["BSFKVAR"].ToString()+dwTmp["BSFKFARBE"].ToString();
                        dwTmp["COMMENTS"] = GetCommentsFromTXTIND("BSF", WarpArticle);
                    }
                    
                }

                /*select txtkpos,txtkon.txtktext from txtind,txtkon
 where  TXTIMANDANT=txtkon.TXTKMANDANT and txtkpind=txtind.txtipind and txtitxtart='BSF' and txtisind like '%PV912102%RD01%'
 order by txtkpos*/
                #endregion

            }
            catch (Exception ex)
            {
                 Tracer.Error(ex.StackTrace, ex); 
            }
            dsTarget.Tables["BSFARB"].AcceptChanges();
            return dsTarget;
        }
        public DataSet GetTechnicalData(DataSet dsTarget, DataRow dwBSFARB)
        {
            try
            {
                //BHSATZ/ BHKART/               BHKVAR
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT BHKART,BHKVAR FROM INTEX.BHSATZ WHERE BHKART = :ArticleNum AND BHKVAR=:ArticleVar";
                orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                orlReadCommand.Parameters.Add("ArticleVar", OracleType.NVarChar).Value = dwBSFARB["BSFKVAR"].ToString();
                 dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "BHSATZ";
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }

            }
            catch (Exception ex)
            {
                 Tracer.Error(ex.StackTrace, ex); 
            }
            return dsTarget;
        }
        public DataSet GetWarpBeamBS09(DataSet dsTarget, DataRow dwBSFARB)
        {
            try
            { 
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT " 
                              + "BAKART"
               + ",BAKVAR"
               + ",BAKFARBE"
               + ",BAKKART"
               + ",BAKKVAR"
               + ",BAKKFARBE"
               + ",BAKPATT"
               + ",BAKPOS"
               + ",BAKANZ"
               + ",BAKABF"
               + " FROM INTEX.BASTKE WHERE BAKART = :ArticleNum AND BAKVAR=:ArticleVar";
                //AND BAKFARBE=:ArticleColor";
                orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                orlReadCommand.Parameters.Add("ArticleVar", OracleType.NVarChar).Value = dwBSFARB["BSFKVAR"].ToString();
                //orlReadCommand.Parameters.Add("ArticleColor", OracleType.NVarChar).Value = dwBSFARB["BSFKFARBE"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "BASTKE";
                    Queue Q10 = new Queue();
                    Q10.Enqueue("BAKABF");
                    DivideBy10(dsResult.Tables[0], Q10);
                    dsTarget = ImportDataTable(dsTarget
                   , FilterByColor(
                   dsResult.Tables["BASTKE"]
                   , "BAKFARBE"
                   , dwBSFARB["BSFKFARBE"].ToString())
                   );
                }
               
                

            }
            catch (Exception ex)
            {
                 Tracer.Error(ex.StackTrace, ex); 
            }
            return dsTarget;
        }
        public DataSet GetWarpYarn(DataSet dsTarget, DataRow dwBSFARB)
        {
            try
            {
                 orlReadCommand = OracleDB.CreateCommand();
                 orlReadCommand.CommandText = "SELECT "
                +"BAGFDZ"
                + ",BAGART"
                + ",BAGVAR"
                + ",BAGFARBE"
                + ",BAGPOS"
                + ",BAGGART"
                + ",BAGGVAR"
                + ",BAGGFARBE"
                + ",BAGFDZ"
                + ",BAGKGNET"
                + ",BAGKGBRUT"
                + ",BAGABF" 
                +" FROM INTEX.BASTGA WHERE BAGART = :ArticleNum AND BAGVAR=:ArticleVar";
                //AND BAGFARBE=:ArticleColor";
                 orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                orlReadCommand.Parameters.Add("ArticleVar", OracleType.NVarChar).Value = dwBSFARB["BSFKVAR"].ToString();
                //orlReadCommand.Parameters.Add("ArticleColor", OracleType.NVarChar).Value = dwBSFARB["BSFKFARBE"].ToString();
                dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "BASTGA";
                    Queue Q10  = new Queue(); 
                    Q10.Enqueue("BAGABF");
                    DivideBy10(dsResult.Tables[0], Q10);
                    Queue Q1000 = new Queue();
                    Q1000.Enqueue("BAGKGNET");
                    Q1000.Enqueue("BAGKGBRUT"); 
                    DivideBy1000(dsResult.Tables[0], Q1000);
                    dsTarget = ImportDataTable(dsTarget
                  , FilterByColor(
                  dsResult.Tables["BASTGA"]
                  , "BAGFARBE"
                  , dwBSFARB["BSFKFARBE"].ToString())
                  );
                    
                } 
            }
            catch (Exception ex)
            {
                 Tracer.Error(ex.StackTrace, ex); 
            }
            return dsTarget;
        }
        public DataSet GetBeamTypeBS31(DataSet dsTarget, DataRow dwBSFARB)
        {
            try
            {
                 orlReadCommand = OracleDB.CreateCommand();
                 orlReadCommand.CommandText = "SELECT "
                + "BSGKETTART"
                + ",BSGMAXLG"
                + ",BSGMAXGW" 
                + ",BSGLLEIST"
                + ",BSGRLEIST"
                + " FROM INTEX.BSGROE WHERE RowNum<2 and BSGART = :ArticleNum";
                 orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                 dsResult = Connect2Proxy(orlReadCommand);
                if (dsResult.Tables.Count > 0)
                {
                    dsResult.Tables[0].TableName = "BSGROE";
                    Queue Q1000 = new Queue();
                    Q1000.Enqueue("BSGMAXLG");
                    Q1000.Enqueue("BSGMAXGW"); 
                    DivideBy1000(dsResult.Tables[0], Q1000);
                    
                }
                foreach (DataTable dtTemp in dsResult.Tables)
                {
                    dsTarget = ImportDataTable(dsTarget, dtTemp);
                }

            }
            catch (Exception ex)
            {
                 Tracer.Error(ex.StackTrace, ex); 
            }
            return dsTarget;
        }
        
        public DataSet BuildWarpMasterData(DataSet dsTarget, DataRow dwBSFARB)
        {
            try
            {
                dsTarget = GetWarpMaster(dsTarget, dwBSFARB);
                dsTarget = GetWarpColorWay(dsTarget, dwBSFARB);
                dsTarget = GetTechnicalData(dsTarget, dwBSFARB);
                dsTarget = GetBeamTypeBS31(dsTarget, dwBSFARB);
                dsTarget = GetWarpBeamBS09(dsTarget, dwBSFARB);
                dsTarget = GetWarpYarn(dsTarget, dwBSFARB);
            }
            catch (Exception ex)
            {
                 Tracer.Error(ex.StackTrace, ex); 
            }
            return dsTarget;
        }


        #region==========EDIT==========================
        public DataSet GetBS03(DataRow dwBSFARB)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {

                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT * FROM INTEX.BSSATZ WHERE BSKART = :ArticleNum";
                orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "BSSATZ";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetBS04(DataRow dwBSFARB)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                //BHSATZ/ BHKART/               BHKVAR
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT* FROM INTEX.BHSATZ WHERE BHKART = :ArticleNum AND BHKVAR=:ArticleVar";
                orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                orlReadCommand.Parameters.Add("ArticleVar", OracleType.NVarChar).Value = dwBSFARB["BSFKVAR"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "BHSATZ";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetBS07(DataRow dwBSFARB)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                #region Color
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT  "
                    + " BSFMANDANT"
                    + ",BSFKART"
                    + ",BSFKVAR"
                    + ",BSFKFARBE "
                    + ",BSFBEZ"
                    + ",BSFBEZ as COMMENTS"
                + " FROM INTEX.BSFARB WHERE BSFKART = :ArticleNum AND BSFKVAR=:ArticleVar";
                //AND BSFKFARBE=:ArticleColor";
                orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                orlReadCommand.Parameters.Add("ArticleVar", OracleType.NVarChar).Value = dwBSFARB["BSFKVAR"].ToString();
                //orlReadCommand.Parameters.Add("ArticleColor", OracleType.NVarChar).Value = dwBSFARB["BSFKFARBE"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "BSFARB";

                }
                setReturn.Merge(setResult);
                #endregion

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }

            return setReturn;
        }
        public DataSet GetBS08(DataRow dwBSFARB)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT * FROM INTEX.BASTGA WHERE BAGART = :ArticleNum AND BAGVAR=:ArticleVar";
                //AND BAGFARBE=:ArticleColor";
                orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                orlReadCommand.Parameters.Add("ArticleVar", OracleType.NVarChar).Value = dwBSFARB["BSFKVAR"].ToString();
                //orlReadCommand.Parameters.Add("ArticleColor", OracleType.NVarChar).Value = dwBSFARB["BSFKFARBE"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "BASTGA";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetBS09(DataRow dwBSFARB)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT * FROM INTEX.BASTKE WHERE BAKART = :ArticleNum AND BAKVAR=:ArticleVar"
                    + " ORDER BY BAKPOS";
                //AND BAKFARBE=:ArticleColor";
                orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                orlReadCommand.Parameters.Add("ArticleVar", OracleType.NVarChar).Value = dwBSFARB["BSFKVAR"].ToString();
                //orlReadCommand.Parameters.Add("ArticleColor", OracleType.NVarChar).Value = dwBSFARB["BSFKFARBE"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "BASTKE";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet GetBS31(DataRow dwBSFARB)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT * FROM INTEX.BSGROE WHERE RowNum<2 and BSGART = :ArticleNum";
                orlReadCommand.Parameters.Add("ArticleNum", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                setResult = Connect2Proxy(orlReadCommand);
                if (setResult.Tables.Count > 0)
                {
                    setResult.Tables[0].TableName = "BSGROE";
                }
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        public DataSet InsertBSSATZ(DataRow dwBSFARB, string BeamType = "S", int SizingBeamYarnEnds = 2000, int numWbeam = 50)
        {
            DataSet set4Return = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            OracleCommand or1;
            try
            {

                #region==========SELECT========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM  BSSATZ"
               + " WHERE "
                + " BSMANDANT=:BSMANDANT AND BSKART=:BSKART";
                or1.Parameters.Add("BSMANDANT", OracleType.NVarChar).Value = dwBSFARB["BSFMANDANT"].ToString();
                or1.Parameters.Add("BSKART", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BSSATZ";
                    if (tbLocal.Rows.Count > 0)
                    {
                        dwLocal = tbLocal.Rows[0];
                        flgExist = true;
                    }
                    else
                    {
                        dwLocal = tbLocal.NewRow();
                    }
                }
                #endregion

                #region==========MAP=========================
                if (dwLocal == null)
                {
                    tbLocal = setINTEX.BSSATZ.Clone();
                    dwLocal = tbLocal.NewRow();
                }
                dwLocal["BSMANDANT"] = dwBSFARB["BSFMANDANT"];
                dwLocal["BSKART"] = dwBSFARB["BSFKART"];
                dwLocal["BSNEUBEN"] = dwBSFARB["BSFNEUB"];
                dwLocal["BSNEUDAT"] = dwBSFARB["BSFNEUD"];
                dwLocal["BSAENDBEN"] = dwBSFARB["BSFNEUB"];
                dwLocal["BSAENDDAT"] = dwBSFARB["BSFNEUD"];
                dwLocal["BSBAUMKZ"] = "K";
                dwLocal["BSVERWEN"] = "Z";
                dwLocal["BSBEZ"] = dwBSFARB["BSFKART"].ToString();
                dwLocal["BSFDZ"] = SizingBeamYarnEnds;
                dwLocal["BSSRFNR"] = "1              ";
                if (BeamType == "W")
                {
                    dwLocal["BSBAUMKZ"] = "Z";
                    dwLocal["BSVERWEN"] = "P";
                }
                #endregion

                #region==========INSERT========================
                if (!flgExist)
                {
                    or1 = OracleDB.CreateCommand();
                    or1.CommandText = " INSERT INTO BSSATZ(BSMANDANT, BSKART, BSAENDZL, BSAENDBEN, BSAENDDAT, BSNEUBEN, BSNEUDAT, BSMTART, BSBEZ, BSSTDKVAR, BSSTAKZ, BSMUSTER, BSFARBVW, BSFARBSTUE, BSPFVARN, BSPROGRP, BSARTGRP, BSPMGRP, BSBAUMKZ, BSVERWEN, BSFDZ, BSFDZLEIST, BSKNOTEN, BSFAKTOR, BSSRFNR, BSCADKEY, BSANZTB, BSKRANF, BSKRENDE, BSKRMITTE, BSFDFLGKR, BSKRART, BSSCHAERBR, BSRIETF, BSFDFLGRI, BSBEM, BSZOLLTNR, BSSUBSTGR, BSHANDICAP, BSAFFINITY, BSSUBST1, BSSUBST2, BSBLEACHED, BSCLORINEST, BSFIXTEMP, BSPRODH, BSMATKL, BSPLGRP, BSPLANART, BSEKGRP, BSBSTME, BSVABME, BSEKWSL, BSINKZ, BSINPR, BSBEWAB, BSFILLER)"
                        + "VALUES (:BSMANDANT, :BSKART, 0, '        ', 0, :BSNEUBEN, :BSNEUDAT, '    ', '                                        ', '1 ', 'A', 'N', 'J', 'J', ' ', '   ', '   ', '    ', :BSBAUMKZ,:BSVERWEN, 0, 0, 'J', 1000, '               ', '                              ', '00', '          ', '          ', ' ', '           ', '          ', 0, 0, '           ', '                                        ', '                    ', '   ', '   ', '               ', '  ', '  ', ' ', ' ', 0, '                  ', '         ', '  ', '                    ', '   ', '   ', ' ', '    ', ' ', 0, 0, '                                                                ')";
                    or1.Parameters.Add("BSMANDANT", OracleType.NVarChar).Value = dwLocal["BSMANDANT"].ToString();
                    or1.Parameters.Add("BSKART", OracleType.NVarChar).Value = dwLocal["BSKART"].ToString();
                    or1.Parameters.Add("BSNEUBEN", OracleType.NVarChar).Value = dwLocal["BSNEUBEN"].ToString();
                    or1.Parameters.Add("BSNEUDAT", OracleType.Number).Value = dwLocal["BSNEUDAT"].ToString();
                    or1.Parameters.Add("BSBAUMKZ", OracleType.NVarChar).Value = dwLocal["BSBAUMKZ"].ToString();
                    or1.Parameters.Add("BSVERWEN", OracleType.NVarChar).Value = dwLocal["BSVERWEN"].ToString();
                    setResult = Connect2Proxy(or1); 
                    
                }
                #endregion

                #region==========UPDATE=======================
                UpdateBSSATZ(dwLocal, BeamType);
                #endregion

                #region==========SELECT========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM  BSSATZ"
               + " WHERE "
                + " BSMANDANT=:BSMANDANT AND BSKART=:BSKART";
                or1.Parameters.Add("BSMANDANT", OracleType.NVarChar).Value = dwLocal["BSMANDANT"].ToString();
                or1.Parameters.Add("BSKART", OracleType.NVarChar).Value = dwLocal["BSKART"].ToString();
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BSSATZ";
                }
                set4Return.Merge(setResult);
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;

        }
        public DataSet InsertBSGROE(DataRow dwBSFARB)
        {
            DataSet set4Return = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            OracleCommand or1;
            try
            {

                #region==========SELECT========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM  BSGROE"
               + " WHERE "
                + " BSGMANDANT=:BSGMANDANT AND BSGART=:BSGART";
                or1.Parameters.Add("BSGMANDANT", OracleType.NVarChar).Value = dwBSFARB["BSFMANDANT"].ToString();
                or1.Parameters.Add("BSGART", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BSGROE";
                    if (tbLocal.Rows.Count > 0)
                    {
                        dwLocal = tbLocal.Rows[0];
                        flgExist = true;
                    }
                    else
                    {
                        dwLocal = tbLocal.NewRow();
                    }
                }
                #endregion

                #region==========MAP=========================
                if (dwLocal == null)
                { 
                    dwLocal = setINTEX.BSGROE.NewBSGROERow();
                }
                dwLocal["BSGMANDANT"] = dwBSFARB["BSFMANDANT"];
                dwLocal["BSGART"] = dwBSFARB["BSFKART"];
                dwLocal["BSGNEUBEN"] = UserID;
                dwLocal["BSGNEUDAT"] = iDate;
                dwLocal["BSGMTYP"] = "WEAVING ";  
               dwLocal["BSGKETTART"] = "PICA  ";
                dwLocal["BSGMAXLG"] = "2500000";
                dwLocal["BSGMAXGW"] = "166000";
                dwLocal["BSGLOS"] = "4";
                #endregion

                #region==========INSERT========================
                if (!flgExist)
                {
                    or1 = OracleDB.CreateCommand();
                    or1.CommandText = " INSERT INTO BSGROE(BSGMANDANT, BSGART, BSGMTYP, BSGLFD, BSGAENDZL, BSGAENDBEN, BSGAENDDAT, BSGNEUBEN, BSGNEUDAT, BSGKETTART, BSGMAXLG, BSGMAXGW, BSGLOS, BSGANZTB, BSGLLEIST, BSGRLEIST, BSGFILLER)"
+ "VALUES (:BSGMANDANT, :BSGART, '        ', 1, 1, '        ', 0, :BSGNEUBEN, :BSGNEUDAT, '      ', 0, 0, 0, 0, '    ', '    ', '                                    ')";
                    or1.Parameters.Add("BSGMANDANT", OracleType.NVarChar).Value = dwLocal["BSGMANDANT"].ToString();
                    or1.Parameters.Add("BSGART", OracleType.NVarChar).Value = dwLocal["BSGART"].ToString();
                    or1.Parameters.Add("BSGNEUBEN", OracleType.NVarChar).Value = dwLocal["BSGNEUBEN"].ToString();
                    or1.Parameters.Add("BSGNEUDAT", OracleType.Number).Value = dwLocal["BSGNEUDAT"].ToString(); 
                    setResult = Connect2Proxy(or1);

                }
                #endregion

                #region==========UPDATE=======================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = " UPDATE  BSGROE SET"
                    + " BSGMTYP=:BSGMTYP,BSGKETTART=:BSGKETTART,BSGMAXLG=:BSGMAXLG,BSGMAXGW=:BSGMAXGW,BSGLOS=:BSGLOS"
                    + " WHERE BSGMANDANT=:BSGMANDANT AND BSGART=:BSGART";
                or1.Parameters.Add("BSGMANDANT", OracleType.NVarChar).Value = dwLocal["BSGMANDANT"].ToString();
                or1.Parameters.Add("BSGART", OracleType.NVarChar).Value = dwLocal["BSGART"].ToString();
                or1.Parameters.Add("BSGMTYP", OracleType.NVarChar).Value = dwLocal["BSGMTYP"].ToString();
                or1.Parameters.Add("BSGKETTART", OracleType.NVarChar).Value = dwLocal["BSGKETTART"].ToString();
                or1.Parameters.Add("BSGMAXLG", OracleType.Number).Value = dwLocal["BSGMAXLG"].ToString();
                or1.Parameters.Add("BSGMAXGW", OracleType.Number).Value = dwLocal["BSGMAXGW"].ToString();
                or1.Parameters.Add("BSGLOS", OracleType.Number).Value = dwLocal["BSGLOS"].ToString(); 
                setResult = Connect2Proxy(or1);
                #endregion

                #region==========SELECT========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM  BSGROE"
                + " WHERE BSGMANDANT=:BSGMANDANT AND BSGART=:BSGART";
                or1.Parameters.Add("BSGMANDANT", OracleType.NVarChar).Value = dwLocal["BSGMANDANT"].ToString();
                or1.Parameters.Add("BSGART", OracleType.NVarChar).Value = dwLocal["BSGART"].ToString();
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BSGROE";
                }
                set4Return.Merge(setResult);
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;

        }
        public DataSet UpdateBSSATZ(DataRow dwInput, string BeamType = "S")
        {
            DataSet set4Return = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            OracleCommand or1;
            try
            {
                dwLocal = dwInput;
                #region==========UPDATE=======================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "UPDATE BSSATZ SET BSBEZ=:BSBEZ,BSFDZ=:BSFDZ,BSSRFNR=:BSSRFNR"
                    + ",BSAENDBEN=:BSAENDBEN,BSAENDDAT=:BSAENDDAT"
                  + " WHERE "
                + " BSMANDANT=:BSMANDANT AND BSKART=:BSKART";
                or1.Parameters.Add("BSMANDANT", OracleType.NVarChar).Value = dwLocal["BSMANDANT"].ToString();
                or1.Parameters.Add("BSKART", OracleType.NVarChar).Value = dwLocal["BSKART"].ToString();
                or1.Parameters.Add("BSBEZ", OracleType.NVarChar).Value = dwLocal["BSBEZ"].ToString();
                or1.Parameters.Add("BSFDZ", OracleType.Number).Value = dwLocal["BSFDZ"].ToString();
                or1.Parameters.Add("BSSRFNR", OracleType.NVarChar).Value = dwLocal["BSSRFNR"].ToString();
                or1.Parameters.Add("BSAENDBEN", OracleType.NVarChar).Value = dwLocal["BSAENDBEN"].ToString();
                or1.Parameters.Add("BSAENDDAT", OracleType.Number).Value = dwLocal["BSAENDDAT"].ToString();
                setResult = Connect2Proxy(or1);
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;

        }
        public DataSet InsertBHSATZ(DataRow dwDispo,DataRow dwBSSATZ, string VAR = "1 ", string BeamType = "S")
        {
            DataSet set4Return = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            OracleCommand or1;
            bool flgExist = false;
            try
            {

                #region==========SELECT========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM BHSATZ"
               + " WHERE BHMANDANT=:BHMANDANT AND BHKART=:BHKART AND BHKVAR=:BHKVAR";
                or1.Parameters.Add("BHMANDANT", OracleType.NVarChar).Value = dwBSSATZ["BSMANDANT"].ToString();
                or1.Parameters.Add("BHKART", OracleType.NVarChar).Value = dwBSSATZ["BSKART"].ToString();
                or1.Parameters.Add("BHKVAR", OracleType.NVarChar).Value = VAR;
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BHSATZ";
                    if (tbLocal.Rows.Count > 0)
                    {
                        dwLocal = tbLocal.Rows[0];
                        flgExist = true;
                    }
                    else
                    {
                        dwLocal = tbLocal.NewRow();
                    }
                }
                #endregion

                #region==========MAP==========================
                if (dwLocal == null)
                {
                    dwLocal = setINTEX.BHSATZ.NewRow();
                }
                dwLocal["BHMANDANT"] = dwBSSATZ["BSMANDANT"].ToString();
                dwLocal["BHKART"] = dwBSSATZ["BSKART"].ToString();
                dwLocal["BHKVAR"] = VAR;
                dwLocal["BHNEUBEN"] = dwBSSATZ["BSNEUBEN"].ToString();
                dwLocal["BHNEUDAT"] = dwBSSATZ["BSNEUDAT"].ToString();
                dwLocal["BHAENDBEN"] = dwBSSATZ["BSNEUBEN"].ToString();
                dwLocal["BHAENDDAT"] = dwBSSATZ["BSNEUDAT"].ToString();
                dwLocal["BHHERART"] = "A";
                if (BeamType == "W")
                {
                    dwLocal["BHHERART"] = "S";
                }
                #endregion

                #region==========INSERT========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = " INSERT INTO BHSATZ(BHMANDANT, BHKART, BHKVAR, BHAENDZL, BHAENDBEN, BHAENDDAT, BHNEUBEN, BHNEUDAT, BHFERTKZ, BHLIEFNR, BHFILLER1, BHLAN, BHKUNDNR, BHHERART, BHSTATUS, BHSTAKZ, BHGASTUE, BHBASTUE, BHAUFTR, BHKGNET, BHLGG, BHRSP, BHSCHLIGR1, BHSCHLIGR2, BHSCHLIGR3, BHSCHLIGR4, BHSCHLIGR5, BHSCHLIGR6, BHSCHLIGR7, BHSCHLIGR8, BHSCHLIGR9, BHSCHLIGR10, BHSCHLIGR11, BHSCHLIGR12, BHSCHLIGR13, BHSCHLIGR14, BHSCHLIGR15, BHSCHLINR1, BHSCHLINR2, BHSCHLINR3, BHSCHLINR4, BHSCHLINR5, BHSCHLINR6, BHSCHLINR7, BHSCHLINR8, BHSCHLINR9, BHSCHLINR10, BHSCHLINR11, BHSCHLINR12, BHSCHLINR13, BHSCHLINR14, BHSCHLINR15, BHCADKEY, BHBEM, BHDEKPR, BHZUGMENG, BHVRPR, BHLEPR, BHBWPR1, BHBWPR2, BHBWPR3, BHMATKTO, BHKTO1, BHEG, BHBON, BHZUSCHL11, BHZUSCHL12, BHZUSCHL13, BHZUSCHL21, BHZUSCHL22, BHZUSCHL23, BHABSABF, BHKALABF, BHAUFKZ, BHKETTLG, BHFERTZT, BHWDBSZT, BHDISPOKZ, BHDISPOZR, BHMINBST, BHMAXBST, BHBSLPKT, BHLO, BHLB, BHABCKL, BHSACHDSP, BHPROF, BHPRUEFANW, BHTAX, BHFILLER)"
                    + "VALUES ("
                + ":BHMANDANT,:BHKART,:BHKVAR, 0, '        ', 0, :BHNEUBEN, :BHNEUDAT, 'E', '        ', '          ', '                    ', '        ', :BHHERART, 'K', ' ', 'V', ' ', 'N', 0, 0, 0, '1', '1', '1', '1', '1', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', '112', '108', '108', '108', '108', '000', '000', '000', '000', '000', '000', '000', '000', '000', '000', '      ', '                                        ', 0, 0, 0, 0, 0, 0, 0, '      ', '      ', ' ', ' ', '        ', '        ', '        ', '        ', '        ', '        ', 10000, 200, ' ', 0, 0, 0, 'B', ' ', 2000, 30000, 0, '  ', '  ', ' ', '        ', 'V', 'J', '   ', '                                                                                                                                            ')";
                or1.Parameters.Add("BHMANDANT", OracleType.NVarChar).Value = dwLocal["BHMANDANT"].ToString();
                or1.Parameters.Add("BHKART", OracleType.NVarChar).Value = dwLocal["BHKART"].ToString();
                or1.Parameters.Add("BHKVAR", OracleType.NVarChar).Value = dwLocal["BHKVAR"].ToString();
                or1.Parameters.Add("BHHERART", OracleType.NVarChar).Value = dwLocal["BHHERART"].ToString();
                or1.Parameters.Add("BHNEUBEN", OracleType.NVarChar).Value = dwLocal["BHNEUBEN"].ToString();
                or1.Parameters.Add("BHNEUDAT", OracleType.Number).Value = dwLocal["BHNEUDAT"].ToString();
                setResult = Connect2Proxy(or1);
                setResult = InsertAPZUORD(dwLocal["BHMANDANT"].ToString(), dwLocal["BHKART"].ToString(), dwLocal["BHKVAR"].ToString(), "K", dwDispo["KAPKRS"].ToString(), dwDispo["KAPNR"].ToString());
                #endregion

                #region==========UPDATE========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "UPDATE BHSATZ SET BHAENDBEN=:BHAENDBEN,BHAENDDAT=:BHAENDDAT"
              + " WHERE BHMANDANT=:BHMANDANT AND BHKART=:BHKART AND BHKVAR=:BHKVAR";
                or1.Parameters.Add("BHMANDANT", OracleType.NVarChar).Value = dwLocal["BHMANDANT"].ToString();
                or1.Parameters.Add("BHKART", OracleType.NVarChar).Value = dwLocal["BHKART"].ToString();
                or1.Parameters.Add("BHKVAR", OracleType.NVarChar).Value = dwLocal["BHKVAR"].ToString();
                or1.Parameters.Add("BHAENDBEN", OracleType.NVarChar).Value = dwLocal["BHAENDBEN"].ToString();
                or1.Parameters.Add("BHAENDDAT", OracleType.Number).Value = dwLocal["BHAENDDAT"].ToString();
                setResult = Connect2Proxy(or1);
                #endregion

                #region==========SELECT========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM BHSATZ"
               + " WHERE BHMANDANT=:BHMANDANT AND BHKART=:BHKART AND BHKVAR=:BHKVAR";
                or1.Parameters.Add("BHMANDANT", OracleType.NVarChar).Value = dwLocal["BHMANDANT"].ToString();
                or1.Parameters.Add("BHKART", OracleType.NVarChar).Value = dwLocal["BHKART"].ToString();
                or1.Parameters.Add("BHKVAR", OracleType.NVarChar).Value = dwLocal["BHKVAR"].ToString();
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BHSATZ";
                }
                set4Return.Merge(setResult);
                #endregion

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        public DataSet InsertBSFARB(DataRow dwBHSATZ, string fARBE = "BLUE      ")
        {
            DataSet set4Return = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            OracleCommand or1;
            bool flgExist = false;
            try
            {
                fARBE = Left(fARBE, 10);

                #region===========SELECT==========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM BSFARB"
               + " WHERE BSFMANDANT=:BSFMANDANT AND BSFKART=:BSFKART AND BSFKVAR=:BSFKVAR AND BSFKFARBE=:BSFKFARBE";
                or1.Parameters.Add("BSFMANDANT", OracleType.NVarChar).Value = dwBHSATZ["BHMANDANT"].ToString();
                or1.Parameters.Add("BSFKART", OracleType.NVarChar).Value = dwBHSATZ["BHKART"].ToString();
                or1.Parameters.Add("BSFKVAR", OracleType.NVarChar).Value = dwBHSATZ["BHKVAR"].ToString();
                or1.Parameters.Add("BSFKFARBE", OracleType.NVarChar).Value = fARBE;

                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BSFARB";
                    if (tbLocal.Rows.Count > 0)
                    {
                        dwLocal = tbLocal.Rows[0];
                        flgExist = true;
                    }
                    else
                    {
                        dwLocal = tbLocal.NewRow();
                    }
                }
                #endregion

                #region===========MAP===========================
                if (dwLocal == null)
                {
                    dwLocal = setINTEX.BSFARB.NewRow();
                }
                dwLocal["BSFMANDANT"] = dwBHSATZ["BHMANDANT"].ToString();
                dwLocal["BSFKART"] = dwBHSATZ["BHKART"].ToString();
                dwLocal["BSFKVAR"] = dwBHSATZ["BHKVAR"].ToString();
                dwLocal["BSFKFARBE"] = fARBE;
                dwLocal["BSFBEZ"] = Left(fARBE + "                                                                             ", 40);

                dwLocal["BSFNEUB"] = dwBHSATZ["BHNEUBEN"].ToString();
                dwLocal["BSFNEUD"] = dwBHSATZ["BHNEUDAT"].ToString();
                dwLocal["BSFAENB"] = dwBHSATZ["BHNEUBEN"].ToString();
                dwLocal["BSFAEND"] = dwBHSATZ["BHNEUDAT"].ToString();

                #endregion

                #region===========INSERT==========================
                if (!flgExist)
                {
                    or1 = OracleDB.CreateCommand();
                    or1.CommandText = "INSERT INTO BSFARB(BSFMANDANT, BSFKART, BSFKVAR, BSFKFARBE, BSFKZKETTF, BSFSTATUS, BSFSFKZ, BSFBEZ, BSFTEXTSCHL, BSFFKARTNR, BSFROHFARBE, BSFMUDESS, BSFUNIFARB, BSFZTWAHL, BSFRUESTGRP, BSFPRUEFGRP, BSFFDVF, BSFPRGRP, BSFGRPBEZ, BSFPREIS, BSFZUABPR, BSFZUABAB, BSFNFART, BSFWERT, BSFKZPA, BSFZUABVH, BSFZUABAK, BSFZUABKU, BSFZUABPROZ, BSFFA, BSFGA, BSFQA, BSFQPKTH, BSFQPKTLH, BSFLOAN, BSFLBAN, BSFLPAN, BSFABKZAN, BSFPKL, BSFVBBRT, BSFBRTTOLPL, BSFBRTTOLMI, BSFVBGRQM, BSFVBGRLFD, BSFGWTOLPL, BSFGWTOLMIN, BSFLAEAEND, BSFGWVERL, BSFRESTSCHL, BSFPHWERTMI, BSFPHWERTMA, BSFWEISSGRD, BSFNETZFVON, BSFNETZFBIS, BSFNETZFSEK, BSFRSSFTRKV, BSFRSSFTRKB, BSFUNIFAEH, BSFFADGERAD, BSFMUSTFRB, BSFVORB, BSFNACHB, BSFMU, BSFMURLOD, BSFMUPROD, BSFMUCTOD, BSFMUDTRL, BSFMUBIRL, BSFLMANRKRS, BSFLMALFDNR, BSFLMAPOS, BSFLODKRS, BSFLODLFD, BSFLODPOS, BSFFILLER, BSFAENZL, BSFAENB, BSFAEND, BSFNEUB, BSFNEUD)"
                        + "VALUES ("
                    + "'" + dwLocal["BSFMANDANT"].ToString() + "', '" + dwLocal["BSFKART"].ToString() + "','" + dwLocal["BSFKVAR"].ToString() + "','" + dwLocal["BSFKFARBE"].ToString() + "', ' ', 'A', ' ','" + dwLocal["BSFBEZ"].ToString() + "', '          ', '          ', '          ', '          ', '          ', 0, '   ', ' ', '   '"
                    + ", ' ', '                    ', 0, 0, 0, '    ', 0, ' ', 0, 0, 0, ' ', 0, 0, 0, 0, 0, '  ', '  ', '        ', ' ', '  ', 0, 0, 0, 0, 0, 0, 0, 0, 0, ' ', 0, 0, 0, 0, 0, 0, 0, 0, ' ', ' ', '          '"
                    + ", '   ', '   ', ' ', '00', '00', ' ', 0, '        ', '  ', '0000', '00', '  ', '000000', '000', '                                                              '"
                    + ", 1, '        ', 0,'" + dwLocal["BSFNEUB"].ToString() + "'," + dwLocal["BSFNEUD"].ToString() + ")";
                    /*
                    or1.Parameters.Add("BSFMANDANT", OracleType.NVarChar).Value = dwLocal["BSFMANDANT"].ToString();
                    or1.Parameters.Add("BSFKART", OracleType.NVarChar).Value = dwLocal["BSFKART"].ToString();
                    or1.Parameters.Add("BSFKVAR", OracleType.NVarChar).Value = dwLocal["BSFKVAR"].ToString();
                    //or1.Parameters.Add("BSFKFARBE", OracleType.NVarChar).Value = dwLocal["BSFKFARBE"].ToString();
                    //or1.Parameters.Add("BSFBEZ", OracleType.NVarChar).Value = dwLocal["BSFBEZ"].ToString();
                    or1.Parameters.Add("BSFNEUB", OracleType.NVarChar).Value = dwLocal["BSFNEUB"].ToString();
                    or1.Parameters.Add("BSFNEUD", OracleType.Number).Value = dwLocal["BSFNEUD"].ToString();*/
                    setResult = Connect2Proxy(or1);
                }
                #endregion

                #region===========UPDATE==========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "UPDATE BSFARB SET BSFAENB=:BSFAENB,BSFAEND=:BSFAEND"
                    + " WHERE BSFMANDANT=:BSFMANDANT AND BSFKART=:BSFKART AND BSFKVAR=:BSFKVAR AND BSFKFARBE=:BSFKFARBE";
                or1.Parameters.Add("BSFMANDANT", OracleType.NVarChar).Value = dwLocal["BSFMANDANT"].ToString();
                or1.Parameters.Add("BSFKART", OracleType.NVarChar).Value = dwLocal["BSFKART"].ToString();
                or1.Parameters.Add("BSFKVAR", OracleType.NVarChar).Value = dwLocal["BSFKVAR"].ToString();
                or1.Parameters.Add("BSFKFARBE", OracleType.NVarChar).Value = dwLocal["BSFKFARBE"].ToString();
                or1.Parameters.Add("BSFAENB", OracleType.NVarChar).Value = dwLocal["BSFAENB"].ToString();
                or1.Parameters.Add("BSFAEND", OracleType.Number).Value = dwLocal["BSFAEND"].ToString();
                setResult = Connect2Proxy(or1);
                #endregion

                #region===========SELECT==========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM BSFARB"
               + " WHERE BSFMANDANT=:BSFMANDANT AND BSFKART=:BSFKART AND BSFKVAR=:BSFKVAR AND BSFKFARBE=:BSFKFARBE";
                or1.Parameters.Add("BSFMANDANT", OracleType.NVarChar).Value = dwLocal["BSFMANDANT"].ToString();
                or1.Parameters.Add("BSFKART", OracleType.NVarChar).Value = dwLocal["BSFKART"].ToString();
                or1.Parameters.Add("BSFKVAR", OracleType.NVarChar).Value = dwLocal["BSFKVAR"].ToString();
                or1.Parameters.Add("BSFKFARBE", OracleType.NVarChar).Value = dwLocal["BSFKFARBE"].ToString();

                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BSFARB";
                }
                set4Return.Merge(setResult);
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        public DataSet InsertBASTKE(DataRow dwBSFARB, DataRow dwBSFARB1, int pos)
        {
            DataSet set4Return = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            OracleCommand or1;
            string PATT = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
            bool flgExist = false;
            try
            {

                #region===========SELECT==========================
                or1 = OracleDB.CreateCommand();
                string POS = Right("00" + pos.ToString(), 2);
                or1.CommandText = "SELECT * FROM BASTKE"
                + " WHERE  BAKMANDANT=:BAKMANDANT AND BAKART=:BAKART AND BAKVAR=:BAKVAR AND BAKFARBE=:BAKFARBE AND BAKPOS=:BAKPOS";
                or1.Parameters.Add("BAKMANDANT", OracleType.NVarChar).Value = dwBSFARB["BSFMANDANT"].ToString();
                or1.Parameters.Add("BAKART", OracleType.NVarChar).Value = dwBSFARB["BSFKART"].ToString();
                or1.Parameters.Add("BAKVAR", OracleType.NVarChar).Value = dwBSFARB["BSFKVAR"].ToString();
                or1.Parameters.Add("BAKFARBE", OracleType.NVarChar).Value = dwBSFARB["BSFKFARBE"].ToString(); ;
                or1.Parameters.Add("BAKPOS", OracleType.NVarChar).Value = POS;

                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BASTKE";
                    if (tbLocal.Rows.Count > 0)
                    {
                        dwLocal = tbLocal.Rows[0];
                        flgExist = true;
                    }
                    else
                    {
                        dwLocal = tbLocal.NewRow();
                    }
                }
                if (dwLocal == null)
                {
                    dwLocal = setINTEX.BASTKE.NewRow();
                }
                #endregion
                #region===========MAP=============================
                dwLocal["BAKMANDANT"] = dwBSFARB["BSFMANDANT"].ToString();
                dwLocal["BAKART"] = dwBSFARB["BSFKART"].ToString();
                dwLocal["BAKVAR"] = dwBSFARB["BSFKVAR"].ToString();
                dwLocal["BAKFARBE"] = dwBSFARB["BSFKFARBE"].ToString();
                dwLocal["BAKPOS"] = POS;
                dwLocal["BAKNEUB"] = dwBSFARB["BSFNEUB"].ToString();
                dwLocal["BAKNEUD"] = dwBSFARB["BSFNEUD"].ToString();
                dwLocal["BAKAENB"] = dwBSFARB["BSFNEUB"].ToString();
                dwLocal["BAKAEND"] = dwBSFARB["BSFNEUD"].ToString();
                dwLocal["BAKKART"] = dwBSFARB1["BSFKART"].ToString();
                dwLocal["BAKKVAR"] = dwBSFARB1["BSFKVAR"].ToString();
                dwLocal["BAKKFARBE"] = dwBSFARB1["BSFKFARBE"].ToString();
                dwLocal["BAKPATT"] = Left(PATT[pos - 1].ToString() + "              ", 6);
                dwLocal["BAKANZ"] = 1;
                #endregion
                #region===========INSERT==========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = " INSERT INTO BASTKE(BAKMANDANT, BAKART, BAKVAR, BAKFARBE, BAKPOS, BAKAENZL, BAKAENB, BAKAEND, BAKNEUB, BAKNEUD, BAKKART, BAKKVAR, BAKKFARBE, BAKPATT, BAKANZ, BAKVARKZ, BAKABF, BAKDISPOKZ, BAKFILLER)"
                    + "VALUES ("
                + ":BAKMANDANT,:BAKART,:BAKVAR, :BAKFARBE, :BAKPOS, 0, '        ', 0, :BAKNEUB, :BAKNEUD, '                    ', '  ', '          ', 'A     ', 4"
                + ", ' ', 0, 'B', '                                            ')";
                or1.Parameters.Add("BAKMANDANT", OracleType.NVarChar).Value = dwLocal["BAKMANDANT"].ToString();
                or1.Parameters.Add("BAKART", OracleType.NVarChar).Value = dwLocal["BAKART"].ToString();
                or1.Parameters.Add("BAKVAR", OracleType.NVarChar).Value = dwLocal["BAKVAR"].ToString();
                or1.Parameters.Add("BAKFARBE", OracleType.NVarChar).Value = dwLocal["BAKFARBE"].ToString();
                or1.Parameters.Add("BAKPOS", OracleType.NVarChar).Value = dwLocal["BAKPOS"].ToString();
                or1.Parameters.Add("BAKNEUB", OracleType.NVarChar).Value = dwLocal["BAKNEUB"].ToString();
                or1.Parameters.Add("BAKNEUD", OracleType.Number).Value = dwLocal["BAKNEUD"].ToString();
                setResult = Connect2Proxy(or1);
                #endregion
                #region===========UPDATE==========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "UPDATE BASTKE"
                    + " SET BAKAENB=:BAKAENB,BAKAEND=:BAKAEND,BAKKART=:BAKKART,BAKKVAR=:BAKKVAR,BAKKFARBE=:BAKKFARBE,BAKPATT=:BAKPATT,BAKANZ=:BAKANZ"
               + " WHERE  BAKMANDANT=:BAKMANDANT AND BAKART=:BAKART AND BAKVAR=:BAKVAR AND BAKFARBE=:BAKFARBE AND BAKPOS=:BAKPOS";
                or1.Parameters.Add("BAKMANDANT", OracleType.NVarChar).Value = dwLocal["BAKMANDANT"].ToString();
                or1.Parameters.Add("BAKART", OracleType.NVarChar).Value = dwLocal["BAKART"].ToString();
                or1.Parameters.Add("BAKVAR", OracleType.NVarChar).Value = dwLocal["BAKVAR"].ToString();
                or1.Parameters.Add("BAKFARBE", OracleType.NVarChar).Value = dwLocal["BAKFARBE"].ToString();
                or1.Parameters.Add("BAKPOS", OracleType.NVarChar).Value = dwLocal["BAKPOS"].ToString();
                or1.Parameters.Add("BAKAENB", OracleType.NVarChar).Value = dwLocal["BAKAENB"].ToString();
                or1.Parameters.Add("BAKAEND", OracleType.Number).Value = dwLocal["BAKAEND"].ToString();
                or1.Parameters.Add("BAKKART", OracleType.NVarChar).Value = dwLocal["BAKKART"].ToString();
                or1.Parameters.Add("BAKKVAR", OracleType.NVarChar).Value = dwLocal["BAKKVAR"].ToString();
                or1.Parameters.Add("BAKKFARBE", OracleType.NVarChar).Value = dwLocal["BAKKFARBE"].ToString();
                or1.Parameters.Add("BAKPATT", OracleType.NVarChar).Value = dwLocal["BAKPATT"].ToString();
                or1.Parameters.Add("BAKANZ", OracleType.NVarChar).Value = dwLocal["BAKANZ"].ToString();

                setResult = Connect2Proxy(or1);
                #endregion
                #region===========SELECT==========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM BASTKE"
               + " WHERE  BAKMANDANT=:BAKMANDANT AND BAKART=:BAKART AND BAKVAR=:BAKVAR AND BAKFARBE=:BAKFARBE AND BAKPOS=:BAKPOS";
                or1.Parameters.Add("BAKMANDANT", OracleType.NVarChar).Value = dwLocal["BAKMANDANT"].ToString();
                or1.Parameters.Add("BAKART", OracleType.NVarChar).Value = dwLocal["BAKART"].ToString();
                or1.Parameters.Add("BAKVAR", OracleType.NVarChar).Value = dwLocal["BAKVAR"].ToString();
                or1.Parameters.Add("BAKFARBE", OracleType.NVarChar).Value = dwLocal["BAKFARBE"].ToString();
                or1.Parameters.Add("BAKPOS", OracleType.NVarChar).Value = dwLocal["BAKPOS"].ToString();

                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BASTKE";
                }
                set4Return.Merge(tbLocal);
                #endregion
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        public DataSet InsertBASTGA(DataRow dwBASTGA)
        {
            DataSet set4Return = new DataSet();
            DataSet setResult = new DataSet();
            DataTable tbLocal = null;
            DataRow dwLocal = null;
            bool flgExist = false;
            OracleCommand or1;
            try
            {

                #region==========SELECT========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "SELECT * FROM BASTGA WHERE  "
                + "  BAGMANDANT=:BAGMANDANT AND BAGART=:BAGART AND BAGVAR=:BAGVAR AND BAGFARBE=:BAGFARBE AND BAGPOS=:BAGPOS";
                or1.Parameters.Add("BAGMANDANT", OracleType.NVarChar).Value = dwBASTGA["BAGMANDANT"].ToString();
                or1.Parameters.Add("BAGART", OracleType.NVarChar).Value = dwBASTGA["BAGART"].ToString();
                or1.Parameters.Add("BAGVAR", OracleType.NVarChar).Value = dwBASTGA["BAGVAR"].ToString();
                or1.Parameters.Add("BAGFARBE", OracleType.NVarChar).Value = dwBASTGA["BAGFARBE"].ToString(); ;
                or1.Parameters.Add("BAGPOS", OracleType.NVarChar).Value = dwBASTGA["BAGPOS"].ToString(); ;

                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count > 0)
                {
                    tbLocal = setResult.Tables[0];
                    tbLocal.TableName = "BASTKE";
                    if (tbLocal.Rows.Count > 0)
                    {
                        flgExist = true;
                        dwLocal = tbLocal.Rows[0];
                    }
                    else
                    {
                        dwLocal = tbLocal.NewRow();
                    }
                }
                #endregion

                #region==========Map Data========================
                dwLocal["BAGMANDANT"] = dwBASTGA["BAGMANDANT"].ToString();
                dwLocal["BAGART"] = dwBASTGA["BAGART"].ToString();
                dwLocal["BAGVAR"] = dwBASTGA["BAGVAR"].ToString();
                dwLocal["BAGFARBE"] = dwBASTGA["BAGFARBE"].ToString();
                dwLocal["BAGPOS"] = dwBASTGA["BAGPOS"].ToString();
                dwLocal["BAGGART"] = dwBASTGA["BAGGART"].ToString();
                dwLocal["BAGGVAR"] = dwBASTGA["BAGGVAR"].ToString();
                dwLocal["BAGGFARBE"] = dwBASTGA["BAGGFARBE"].ToString();
                dwLocal["BAGFDZ"] = dwBASTGA["BAGFDZ"].ToString();
                dwLocal["BAGABF"] = dwBASTGA["BAGABF"].ToString();
                dwLocal["BAGKGNET"] = dwBASTGA["BAGKGNET"].ToString();
                dwLocal["BAGKGBRUT"] = dwBASTGA["BAGKGBRUT"].ToString();
                dwLocal["BAGDISPOKZ"] = dwBASTGA["BAGDISPOKZ"].ToString();
                dwLocal["BAGBEM"] = dwBASTGA["BAGBEM"].ToString();
                dwLocal["BAGVARKZ"] = dwBASTGA["BAGVARKZ"].ToString();
                dwLocal["BAGAUFMSL"] = dwBASTGA["BAGAUFMSL"].ToString();
                dwLocal["BAGFILLER"] = dwBASTGA["BAGFILLER"].ToString();
                #endregion

                #region==========INSERT========================
                if (!flgExist)
                {
                    or1 = OracleDB.CreateCommand();
                    or1.CommandText = " INSERT INTO BASTGA(BAGMANDANT, BAGART, BAGVAR, BAGFARBE, BAGPOS, BAGGART, BAGGVAR, BAGGFARBE, BAGFDZ, BAGABF, BAGKGNET, BAGKGBRUT, BAGDISPOKZ, BAGBEM, BAGVARKZ, BAGAUFMSL, BAGFILLER)"
                        + "VALUES (:BAGMANDANT, :BAGART, :BAGVAR, :BAGFARBE, :BAGPOS, 'J100                ', '3  ', '          ', 8308, 0, 98126, 98126, 'B', '                    ', ' ', '  ', '                  ')";
                    or1.Parameters.Add("BAGMANDANT", OracleType.NVarChar).Value = dwLocal["BAGMANDANT"].ToString();
                    or1.Parameters.Add("BAGART", OracleType.NVarChar).Value = dwLocal["BAGART"].ToString();
                    or1.Parameters.Add("BAGVAR", OracleType.NVarChar).Value = dwLocal["BAGVAR"].ToString();
                    or1.Parameters.Add("BAGFARBE", OracleType.NVarChar).Value = dwLocal["BAGFARBE"].ToString();
                    or1.Parameters.Add("BAGPOS", OracleType.NVarChar).Value = dwLocal["BAGPOS"].ToString();
                    setResult = Connect2Proxy(or1);
                }
                #endregion

                #region==========UPDATE========================
                or1 = OracleDB.CreateCommand();
                or1.CommandText = "UPDATE BASTGA SET BAGGART=:BAGGART,BAGGVAR=:BAGGVAR , BAGGFARBE=:BAGGFARBE"
                + ", BAGFDZ=:BAGFDZ, BAGABF=:BAGABF"
                + ",BAGKGNET=:BAGKGNET,BAGKGBRUT=:BAGKGBRUT"
                    + " WHERE "
                      + "  BAGMANDANT=:BAGMANDANT AND BAGART=:BAGART AND BAGVAR=:BAGVAR AND BAGFARBE=:BAGFARBE AND BAGPOS=:BAGPOS";
                or1.Parameters.Add("BAGMANDANT", OracleType.NVarChar).Value = dwLocal["BAGMANDANT"].ToString();
                or1.Parameters.Add("BAGART", OracleType.NVarChar).Value = dwLocal["BAGART"].ToString();
                or1.Parameters.Add("BAGVAR", OracleType.NVarChar).Value = dwLocal["BAGVAR"].ToString();
                or1.Parameters.Add("BAGFARBE", OracleType.NVarChar).Value = dwLocal["BAGFARBE"].ToString();
                or1.Parameters.Add("BAGPOS", OracleType.NVarChar).Value = dwLocal["BAGPOS"].ToString();
                or1.Parameters.Add("BAGGART", OracleType.NVarChar).Value = dwLocal["BAGGART"].ToString();
                or1.Parameters.Add("BAGGVAR", OracleType.NVarChar).Value = dwLocal["BAGGVAR"].ToString();
                or1.Parameters.Add("BAGGFARBE", OracleType.NVarChar).Value = dwLocal["BAGGFARBE"].ToString();
                or1.Parameters.Add("BAGFDZ", OracleType.Number).Value = dwLocal["BAGFDZ"].ToString();
                or1.Parameters.Add("BAGABF", OracleType.Number).Value = dwLocal["BAGABF"].ToString();
                or1.Parameters.Add("BAGKGNET", OracleType.Number).Value = dwLocal["BAGKGNET"].ToString();
                or1.Parameters.Add("BAGKGBRUT", OracleType.Number).Value = dwLocal["BAGKGBRUT"].ToString();

                #endregion
                setResult = Connect2Proxy(or1);

            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return set4Return;
        }
        public void ComputeWarpQuantity(DataRow dwBSSATZ, ref DataRow dwBASTGA)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            OracleCommand or1 = OracleDB.CreateCommand();
            string wPattern = "", GSVAR, GSART, GSRECHNUM;
            double in2cm, mt2cm, mtr2yd, pound2kg;
            double CountYarn, lenYarn, BAGFDZ, BSFAKTOR, GSRECHNR, WTBSRAPP, BAGKGNET, BAGKGBRUT, BAGABF;
            in2cm = 2.54;
            mt2cm = 100.00;
            mtr2yd = 0.9144;
            pound2kg = 0.4536;
            try
            {
                //=E28*E37 在某经轴上的根数*标准长度
                //=(E40/E35)/(E34*840)*E36
                //米到码 纱支 磅到公斤

                BAGFDZ = System.Convert.ToDouble(dwBASTGA["BAGFDZ"].ToString());
                BSFAKTOR = System.Convert.ToDouble(dwBSSATZ["BSFAKTOR"].ToString());
                GSART = dwBASTGA["BAGGART"].ToString();
                GSVAR = Left(dwBASTGA["BAGGVAR"].ToString() + "   ", 3);
                or1.CommandText = "SELECT * FROM GSDAT WHERE GSMANDANT='00' AND GSART='" + GSART + "' AND GSVAR='" + GSVAR + "'";
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count == 0)
                {
                    return;
                }
                GSRECHNUM = setResult.Tables[0].Rows[0]["GSRECHNUM"].ToString();
                GSRECHNR = System.Convert.ToDouble(setResult.Tables[0].Rows[0]["GSRECHNR"].ToString()) * 0.0001;
                GSRECHNR = TONE(GSRECHNR, GSRECHNUM.Trim());
                wPattern = dwBSSATZ["BSSRFNR"].ToString();
                or1.CommandText = "SELECT * FROM WTSAER WHERE WTBMANDANT='00' AND WTBSRFNR='" + wPattern + "'";
                setResult = Connect2Proxy(or1);
                if (setResult.Tables.Count == 0)
                {
                    return;
                }
                WTBSRAPP = System.Convert.ToDouble(setResult.Tables[0].Rows[0]["WTBSRAPP"].ToString());
                lenYarn = BAGFDZ * BSFAKTOR;
                //=(E40/E35)/(E34*840)*E36   //米到码 纱支 磅到公斤
                BAGKGNET = (lenYarn / mtr2yd) / (GSRECHNR * 840) * pound2kg;
                BAGKGNET = System.Math.Round(BAGKGNET * 1000, 0);
                dwBASTGA["BAGKGNET"] = BAGKGNET.ToString().Replace(".0", "");
                BAGABF = System.Convert.ToDouble(dwBASTGA["BAGABF"].ToString()) * 0.01 * 0.01;
                BAGKGBRUT = BAGKGNET * (1 + BAGABF);
                BAGKGBRUT = System.Math.Round(BAGKGBRUT, 0);
                dwBASTGA["BAGKGBRUT"] = BAGKGBRUT.ToString().Replace(".0", "");
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
        }

        public DataSet GetData(DataRow dwBSFARB)
        {
            DataSet setResult = new DataSet();
            DataSet setReturn = new DataSet();
            try
            {
                setResult = GetBS03(dwBSFARB);
                setReturn.Merge(setResult);
                setResult = GetBS07(dwBSFARB);
                setReturn.Merge(setResult);
                setResult = GetBS04(dwBSFARB);
                setReturn.Merge(setResult);
                setResult = GetBS31(dwBSFARB);
                setReturn.Merge(setResult);
                setResult = GetBS09(dwBSFARB);
                setReturn.Merge(setResult);
                setResult = GetBS08(dwBSFARB);
                setReturn.Merge(setResult);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
        #endregion
    }
}